package com.threeti.mecool.core.infrastructure.persistence.plan;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.threeti.mecool.core.domain.model.plan.PlanJobResult;

public interface PlanJobResultRepository extends JpaRepository<PlanJobResult, Long>, JpaSpecificationExecutor<PlanJobResult> {

    List<PlanJobResult> findByJobNameAndChannelChannelCityAdCode(String jobName, String cityAdCode);

    List<PlanJobResult> findByJobNameAndChannelChannelProvinceAdCode(String jobName, String provinceAdCode);

    List<PlanJobResult> findByJobStatusAndChannelChannelAdCode(String jobStatus, String adCode);

    List<PlanJobResult> findByJobStatusAndChannelChannelCityAdCode(String jobStatus, String cityAdCode);

    List<PlanJobResult> findByJobStatusAndChannelChannelProvinceAdCode(String jobStatus, String provinceAdCode);

    List<PlanJobResult> findByJobNameAndJobStatusAndChannelChannelCityAdCode(String jobName, String jobStatus, String cityAdCode);

    List<PlanJobResult> findByJobNameAndJobStatusAndChannelChannelProvinceAdCode(String jobName, String jobStatus, String provinceAdCode);

    List<PlanJobResult> findByPlanJob_PlanProject_Plan_UserLoginNameAndFinishedAtIsNull(String userLoginName);

    @Query("from PlanJobResult where empId = ?1 and channel.channel.cityAdCode = ?2 and planJobStartTime between ?3 and ?4 order by planJobStartTime")
    List<PlanJobResult> findByEmpIdAndChannelChannelCityAdCodeOrderByPlanJobStartTimeAndPlanTimeBetween(String empId, String cityAdCode, Date startTime, Date endTime);

    @Query("from PlanJobResult where jobName = ?1 and empId = ?2 and channel.channel.cityAdCode = ?3 order by planJobStartTime")
    List<PlanJobResult> findByJobNameAndEmpIdAndChannelChannelCityAdCodeOrderByPlanJobStartTime(String jobName, String empId, String cityAdCode);

    List<PlanJobResult> findByEmpId(String empId);

    List<PlanJobResult> findByEmpIdAndJobStatus(String empId, String status);

    Page<PlanJobResult> findByEmpId(String empId, Pageable pageable);

    Page<PlanJobResult> findByEmpIdAndJobStatus(String empId, String status, Pageable pageable);

    @Query("select r from PlanJobResult r where r.empId IN :empIds order by r.planJobStartTime desc, r.finishedAt desc")
    Page<PlanJobResult> findByEmpIdIn(@Param("empIds") List<String> empIds, Pageable pageable);

    @Query("select r from PlanJobResult r where r.jobStatus = :jobStatus and r.empId IN :empIds order by r.planJobStartTime desc, r.finishedAt desc")
    Page<PlanJobResult> findByEmpIdInAndJobStatus(@Param("empIds") List<String> empIds, @Param("jobStatus") String status, Pageable pageable);

    @Query("select r from PlanJobResult r where r.empId IN :empIds and r.projectCode IN :projectCodes order by r.planJobStartTime desc, r.finishedAt desc")
    Page<PlanJobResult> findByEmpIdInAndProjectCodeIn(@Param("empIds") List<String> empIds, @Param("projectCodes") List<String> projectCodes, Pageable pageable);

    @Query("select r from PlanJobResult r where r.jobStatus = :jobStatus and r.empId IN :empIds and r.projectCode IN :projectCodes order by r.planJobStartTime desc, r.finishedAt desc")
    Page<PlanJobResult> findByEmpIdInAndJobStatusAndProjectCodeIn(@Param("empIds") List<String> empIds, @Param("jobStatus") String status, @Param("projectCodes") List<String> projectCodes, Pageable pageable);

    List<PlanJobResult> findByJobCodeAndProjectCodeAndStoreCodeAndJobStatus(String jobCode, String projectCode, String storeCode, String jobStatus);

    List<PlanJobResult> findByPlanJob_Job_CodeAndProjectCodeAndStoreCodeAndJobStatus(String jobCode, String projectCode, String storeCode, String jobStatus);

    @Query("from PlanJobResult where planJob.planProject.plan.id = ?1 and (finishedAt is not null or jobStatus = '" + PlanJobResult.JOB_STATUS_FINISHED + "')")
    List<PlanJobResult> findByPlanJob_PlanProject_Plan_IdAndFinishedAtIsNotNullOrJobStatusFinished(Long planId);

    List<PlanJobResult> findByPlanJob_PlanProject_Plan_IdAndFinishedAtIsNotNull(Long planId);

    PlanJobResult findByPlanJob_IdAndFinishedAtIsNotNull(Long planJobId);
    
    @Query("from PlanJobResult where id IN :ids")
    List<PlanJobResult> findByIds(@Param("ids") List<Long> ids);

    @Query(nativeQuery=true,value="select t.channel,to_char(t.finished_at, 'yyyy-MM-dd HH:mm:ss') from DM_WF_PLAN_JOB_RESULT t where t.job_code = 'WF_JOB_SO' and t.job_status = 'FINISHED' and t.channel in (?1) ")
	List findChannelByChannelId(List<String> channelIds);
    
}
